import json
from ..model.User import User
from . import db

# 查询
def getUser(id=None):
    sql = ''
    if not id:
        # 查询所有结果
        sql = 'select * from userTb;'
        rows = db.query(sql)
        users = []
        for row in rows:
            item = User(row[0],row[1],row[2],row[3],row[4],row[5])
            users.append(item.toDic())
        return json.dumps(users)
    else:
        # 查询单个结果
        sql = f"select * from userTb where id={id};"
        # 读取记录
        row = db.query(sql).fetchone()
        # 如果查询结果为空
        if not row:
            return "null"
        item = User(row[0],row[1],row[2],row[3],row[4],row[5])
        return json.dumps(item.toDic())

# 修改
def setUser(user:User):
    sql = f"update userTb set sid=?,name=?,qq=?,autocard=?,sendEmail=? where id={user.id};"
    # 返回值应该是1
    if db.goSQL(sql,user.toCell()[1:]):
        return True
    else:
        return False

def setUserSid(id,sid):
    sql = f"update userTb set sid='{sid}' where id={id};"
    if db.goSQL(sql):
        return True
    else:
        return False

# 新增
def addUser(user:User):
    sql = f"insert into userTb (id,sid,name,qq,autocard,sendEmail) values(?,?,?,?,?,?);"
    # 返回值应该是1
    if db.goSQL(sql,user.toCell()):
        return True
    else:
        return False

# 删除
def delUser(id):
    sql = f"delete from userTb where id={id};"
    # 返回值应该是1
    if db.goSQL(sql):
        return True
    else:
        return False

# 打卡用数据
def getCardData():
    sql = "select * from cardData;"
    data = []
    rows = db.query(sql)
    for row in rows:
        data.append({
            'id':row[0],
            'name':row[1],
            'qq':row[2],
            'autocard':row[3],
            'sendEmail':row[4],
            'sname':row[5],
            'site':row[6],
            'province':row[7],
            'city':row[8],
            'district':row[9],
            'street':row[10]
        })
    return json.dumps(data)